﻿using Dapper;
using System.Collections.Generic;
using System.Linq;
using zhangmenren.Db;
using zhangmenren.Models;
using zhangmenren.Models.ViewModle;
using zhangmenren.Models.Enums;

namespace zhangmenren.Repository
{
	public class WorksRepository : BaseRepository
	{
		public int Add(WorksEntity entity)
		{
			string sql = @"INSERT INTO Works 
                            (AuthorId,Author
                            ,[Type]
                            ,[State],
                            Title,
                            Price,
                            Describe,
                            CoverImg,
                            ClassId,[Sort],
                            IsRecommend,
                            AddDT)
                        VALUES
                            (@AuthorId,@Author
                            ,@Type,@State,@Title,@Price,@Describe,@CoverImg,@ClassId,@Sort,@IsRecommend
                            ,GETDATE())";
			return Execute(connection => connection.Execute(sql, entity));
		}

		public int Del(int id)
		{
			string sql = @"DELETE  FROM Works WHERE Id = @Id;DELETE  FROM Chapter WHERE WorkId = @Id";
			return Execute(connection => connection.Execute(sql, new { Id = id }));
		}


		public int EditState(int id, bool state)
		{
			string sql = @"UPDATE Works SET State = @State WHERE Id = @Id";
			return Execute(connection => connection.Execute(sql, new { Id = id, State = state }));
		}

        public int EditSort(int id, int sort)
        {
            string sql = @"UPDATE Works SET [Sort] = @Sort WHERE Id = @Id";
            return Execute(connection => connection.Execute(sql, new { Id = id, Sort = sort }));
        }

        public int IsXianShi(int id, bool isrecommend)
		{
			string sql = @"UPDATE Works SET IsRecommend = @IsRecommend WHERE Id = @Id";
			return Execute(connection => connection.Execute(sql, new { Id = id, IsRecommend = isrecommend }));
		}

        public IEnumerable<WorksModel> Select(WorkType type,string key,int num)
        {
            string sql = "";
            string keystr = "";
            if (!string.IsNullOrWhiteSpace(key))
            {
                keystr = @"AND Works.Title LIKE '%" + key + "%'";
            }
            sql = @"SELECT Works.Id,Author,[Sort],Works.Price,Works.Describe,Works.IsRecommend,Works.AddDT,Works.CoverImg,Works.[State],Works.Title,(SELECT COUNT(Id) FROM Census WHERE Census.WorkId =Works.Id AND type=3) AS GouMaiNum,[Type],Classify.Title as ClassName,(SELECT COUNT(Chapter.Id) from Chapter WHERE Chapter.WorkId=Works.Id) as Chapter FROM Works join Classify on Works.ClassId=Classify.Id WHERE Works.Type=@Type " + keystr + " ORDER BY [Sort] DESC ";
            var result= Execute(connection => connection.Query<WorksModel>(sql, new { Type = type }));
            if (num!=0)
            {
               return  result.Skip(0).Take(num);

            }
            return result;  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }

        public IEnumerable<WorksModel> Select(int index=1, int page=50, string key="")
		{
			string sql = "";
			string keystr = "";
			if (!string.IsNullOrWhiteSpace(key))
			{
				keystr = @" WHERE Works.Title LIKE '%" + key + "%'";
			}
			sql = @"SELECT Works.Id,Author,[Sort],Works.Price,Works.Describe,Works.IsRecommend,Works.AddDT,Works.CoverImg,Works.[State],Works.Title,(SELECT COUNT(Id) FROM Census WHERE Census.WorkId =Works.Id AND type=3) AS GouMaiNum,[Type],Classify.Title as ClassName,(SELECT COUNT(Chapter.Id) from Chapter WHERE Chapter.WorkId=Works.Id) as Chapter FROM Works join Classify on Works.ClassId=Classify.Id" + keystr +" ORDER BY [Sort] DESC ";
			return Execute(connection => connection.Query<WorksModel>(sql)).Skip((index - 1) * page).Take(page); ;  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
		}

		public IEnumerable<WorksModel> SelectTuiJian()
		{
			string sql = @"SELECT Id,Describe,IsRecommend,AddDT,CoverImg,[State],AuthorId  FROM Works WHERE IsRecommend = 1 AND Type = 2  ORDER BY [Sort] DESC ";
			return Execute(connection => connection.Query<WorksModel>(sql));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
		}

		public IEnumerable<WorksModel> FindById(int id)
		{
			string sql = @"SELECT Works.Id,Author,Works.Describe,Works.Title,Works.Price,Works.IsRecommend,Works.AddDT,Works.CoverImg,Works.[State],AuthorId,(SELECT COUNT(Id) FROM Census WHERE Census.WorkId =Works.Id AND type=0) AS ShouChangNum  FROM Works WHERE Id=@Id ";
			return Execute(connection => connection.Query<WorksModel>(sql, new { Id = id }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
		}

		public IEnumerable<WorksEntity> FindByZMId(int zmid)
		{
			string sql = @"SELECT Works.Id,Works.Describe,Works.IsRecommend,Works.AddDT,Works.CoverImg,Works.[State],AuthorId  FROM Works WHERE AuthorId=@AuthorId";
			return Execute(connection => connection.Query<WorksModel>(sql, new { AuthorId = zmid }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
		}

		public IEnumerable<TuiJianModel> SelectWorks()
		{
			string sql = @"SELECT Works.Id,Works.Title,Chapter.Title as ChapterTitle, Works.AuthorId,Works.Price,Works.CoverImg,Url,Sort FROM Chapter join Works on Chapter.WorkId=Works.Id ORDER BY Sort DESC";
			return Execute(connection => connection.Query<TuiJianModel>(sql));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
		}

		
		public IEnumerable<WorksModel> FindByAuthor(int authorId)
		{
			string sql = @"SELECT Works.Title,Works.Price,Works.CoverImg,(SELECT COUNT(Id) FROM Census WHERE Census.WorkId =Works.Id AND type=3) AS GouMaiNum FROM Works WHERE Works.AuthorId=@AuthorId  ORDER BY [Sort] DESC ";
			return Execute(connection => connection.Query<TuiJianModel>(sql,new { AuthorId=authorId }));
		}

	}
}
